Oracle创建主键的三种方式

2024-06-03 11:19| 来源: 网络整理| 查看: 265

Oracle中创建主键，可以有几种方式。

第一种，在建表的时候同时指定主键，

代码语言：javascript复制SQL> create table t_pk_01 (id number, constraint pk_id_01 primary key(id)); Table created.

创建主键约束的同时，他会自动创建一个唯一索引，

代码语言：javascript复制SQL> select table_name, constraint_name, constraint_type from user_constraints where table_name='T_PK_01'; TABLE_NAME CONSTRAINT_NAME C ------------------------------ ------------------------------ - T_PK_01 PK_ID_01 P SQL> select table_name, index_name, uniqueness from user_indexes where table_name='T_PK_01'; TABLE_NAME INDEX_NAME UNIQUENES ------------------------------ ------------------------------ --------- T_PK_01 PK_ID_01 UNIQUE

‍

第二种，创建表，再alter table增加主键约束，

代码语言：javascript复制SQL> create table t_pk_02 (id number); Table created. SQL> alter table t_pk_02 add constraint pk_id_02 primary key (id); Table altered.

我们从10046来看下alter table到底做了什么，

代码语言：javascript复制SQL> alter session set events '10046 trace name context forever, level 12'; Session altered. SQL> alter session set tracefile_identifier='bisal'; Session altered. SQL> alter table t_pk_02 add constraint pk_id_02 primary key (id); Table altered. SQL> alter session set events '10046 trace name context off'; Session altered. SQL> select distinct(m.sid),p.pid,p.tracefile from v$mystat m,v$session s,v$process p where m.sid=s.sid and s.paddr=p.addr; SID PID TRACEFILE ---------- ----------- ------------------------------------------------------------------------------- 189 22 /u01/app/oracle/diag/rdbms/dcm/DCM/trace/DCM_ora_18653_bisal.trc

从trace我们能看到，对T_PK_02加了share模式锁，指定nowait，先创建的约束，然后创建了唯一索引，

代码语言：javascript复制... LOCK TABLE "T_PK_02" IN SHARE MODE NOWAIT ... alter table t_pk_02 add c ... update con$ ... ... insert into con$ ... ... CREATE UNIQUE INDEX "BISAL"."PK_ID_02" on "BISAL"."T_PK_02"("ID") NOPARALLEL

第三种，分开创建主键约束和主键索引。

主要有两个场景。

(1) 当使用CTAS创建表时，

代码语言：javascript复制SQL> create table t_pk_03 as select * from t_pk_01; Table created.

主键约束并未带过来，

代码语言：javascript复制SQL> select table_name, constraint_name, constraint_type from user_constraints where table_name='T_PK_03'; no rows selected SQL> select table_name, index_name, uniqueness from user_indexes where table_name='T_PK_03'; no rows selected

此时如果表中存在很多的数据，直接使用方法2，可能会带来两个问题，

1. 创建唯一索引的用时。

2. 唯一索引允许包含空值，因为主键约束不允许空值，还需要判断字段是否为空的用时。

对(1)，从trace，我们可以看到，默认创建唯一索引的时候，并未指定online，因此用时取决于数据量。

对(2)，如果字段设置NOT NULL，应该不需要判断，如果没设置，则需要判断字段中是否含空值，还是取决于表的数据量。

因此，可以选择先在线创建唯一索引，再增加主键约束，从trace能看到，此时增加主键约束的过程中，不会再操作索引，

代码语言：javascript复制SQL> create unique index pk_id_03 on t_pk_03(id) online; Index created. SQL> alter table t_pk_03 add constraint pk_id_03 primary key (id); Table altered.

(2) 往往在生产环境，数据表空间和索引表空间是分开的，如果采用第一种和第二种的方式，主键索引会创建在执行用户的默认表空间，很可能是数据表空间，因此分开创建，还可以在创建索引的时候，指定tablespace，明确索引表空间，

代码语言：javascript复制SQL> create unique index pk_id_03 on t_pk_03(id) tablespace xxx; Index created.

【本文地址】

公司简介

联系我们